﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using CrystalDecisions.CrystalReports.Engine;

namespace UdrinkWater_Project.Officer
{
    public partial class StockReport : System.Web.UI.Page
    {
        private string Connection = ConfigurationManager.ConnectionStrings["DB_Udrink"].ConnectionString;
        protected void Page_Load(object sender, EventArgs e)
        {
            string Stock_ID = Session["StockDateID"].ToString();
            string list = Session["list"].ToString();
            //string a = Session["DocDate"].ToString();
            //DateTime docDate = Convert.ToDateTime(a);

            //string DocDate = ConvestToDateN((docDate.Date).ToShortDateString());
            //string b = Session["DeeadLine"].ToString();
            //DateTime DeadLine = Convert.ToDateTime(a);
            //string DeeadLine = ConvestToDateN((DeadLine.Date).ToShortDateString());
            //string PrintDate = ConvestToDateN((DateTime.Now).ToShortDateString());


            SqlConnection objConn = new SqlConnection();
            SqlCommand objCmd = new SqlCommand();
            SqlDataAdapter dtAdapter = new SqlDataAdapter();

            DataSet ds = new DataSet();
            DataTable dt = null;
            string strConnString = null;
            StringBuilder strSQL = new StringBuilder();
         
            strSQL.Append("SELECT *, ROW_NUMBER() OVER(ORDER BY Product_Date) ROW ");
            strSQL.Append("FROM (SELECT TB_StockProductIN.Product_Date, TB_StockProductIN.Product_ID, TB_Product.Product_name, TB_Unit_Category.Unit_Name, TB_StockProductIN.Product_Count, TB_StockProductIN.Act_Officer,");
            strSQL.Append("(TB_Employee.Prefix +''+ TB_Employee.Firstname +' '+ TB_Employee.Lastname) as EmpName,'เข้าคลัง' AS StatusTranfer ");
            strSQL.Append("FROM TB_StockProductIN ");
            strSQL.Append("LEFT JOIN TB_Employee ON TB_StockProductIN.Act_Officer = TB_Employee.ID_Card ");
            strSQL.Append("LEFT JOIN TB_Product ON TB_StockProductIN.Product_ID = TB_Product.Product_ID ");
            strSQL.Append("LEFT JOIN TB_Unit_Category ON TB_Product.Unit = TB_Unit_Category.Unit_ID");
            strSQL.Append(" WHERE TB_StockProductIN.Product_Date ='" + Stock_ID + "' ");
            strSQL.Append(" UNION ");
            strSQL.Append("SELECT TB_StockProductOUT.Product_Date, TB_StockProductOUT.Product_ID, TB_Product.Product_name, TB_Unit_Category.Unit_Name, TB_StockProductOUT.Product_Count, TB_StockProductOUT.Act_Officer, ");
            strSQL.Append("(TB_Employee.Prefix +''+ TB_Employee.Firstname +' '+ TB_Employee.Lastname) as EmpName,'ออกคลัง' AS StatusTranfer ");
            strSQL.Append("FROM TB_Product ");
            strSQL.Append("LEFT JOIN TB_Unit_Category ON TB_Product.Unit = TB_Unit_Category.Unit_ID LEFT JOIN ");
            strSQL.Append("TB_StockProductOUT ");
            strSQL.Append("LEFT JOIN TB_Employee ON TB_StockProductOUT.Act_Officer = TB_Employee.ID_Card ON TB_Product.Product_ID = TB_StockProductOUT.Product_ID ");
            strSQL.Append("WHERE TB_StockProductOUT.Product_Date  ='" + Stock_ID + "') a");
            strSQL.Append(" order by Product_ID asc");
           // strSQL.Append("order by Product_ID asc");
            
            objConn.ConnectionString = Connection;
            var _with1 = objCmd;
            _with1.Connection = objConn;
            _with1.CommandText = strSQL.ToString();
            _with1.CommandType = CommandType.Text;
            dtAdapter.SelectCommand = objCmd;

            dtAdapter.Fill(ds, "CrystalReportStock");
            dt = ds.Tables[0];

            dtAdapter = null;
            objConn.Close();
            objConn = null;

            ReportDocument rpt = new ReportDocument();
            rpt.Load(Server.MapPath("CrystalReportStock.rpt"));
            rpt.SetDataSource(dt);
            rpt.SetParameterValue("list", list);
            //this.CrystalReportViewer1.ReportSource = rpt;
            rpt.ExportToHttpResponse
            (CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, Response, true, "Stock");
        }

        
    }
}